package com.boesch.electroOffice.dataaccess.daoImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.ArticleDao;
import com.boesch.electroOffice.dataaccess.dao.IdentifierDao;
import com.boesch.electroOffice.dataaccess.mapper.EldasArticleMapper;
import com.boesch.electroOffice.dataaccess.mapper.messerli.SteigerArticleMapper;
import com.boesch.electroOffice.model.electroOffice.EldasArticle;
import com.boesch.electroOffice.model.messerli.SteigerArticle;
import com.boesch.electroOffice.utils.DateUtils;
import com.boesch.electroOffice.utils.UserUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the electroOffice database for class Material.
 * If a Exception is thrown, 
 * it will be unpacked to a illegalDataAccessException 
 * including a return code which can be shown to the user.
 * 
 * @author Ralph Koster
 * 
 */
@Repository
public class ArticleDaoImpl implements ArticleDao, Serializable {

	private static final long serialVersionUID = -7997543548249309904L;
	private static final Log log = LogFactoryUtil.getLog(ArticleDaoImpl.class);

	@Autowired
	private IdentifierDao identifierDao;
	@Autowired
	private JdbcTemplate eoJdbcTemplate;
	@Autowired
	private JdbcTemplate msJdbcTemplate;

	@Value("${electroOffice.queryReadArticlesByAppointmentId}")
	private String queryReadArticlesByAppointmentId;
	@Value("${electroOffice.queryReadAllEldasArticles}")
	private String queryReadAllEldasArticles;
	@Value("${electroOffice.queryCreateEldasArticle}")
	private String queryCreateEldasArticle;
	@Value("${electroOffice.queryCreateSteigerArticle}")
	private String queryCreateSteigerArticle;

	@Value("${messerli.queryReadAllSteigerArticles}")
	private String queryReadAllSteigerArticles;

	/**
	 * {@inheritDoc}
	 */
	public List<EldasArticle> readAllEldasArticles() throws IllegalDataAccessException {
		List<EldasArticle> articles = new ArrayList<EldasArticle>();
		try {
			articles = eoJdbcTemplate.query(queryReadAllEldasArticles, new Object[] {}, new EldasArticleMapper(identifierDao));
			return articles;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<SteigerArticle> readAllSteigerArticles() throws IllegalDataAccessException {
		List<SteigerArticle> material = new ArrayList<SteigerArticle>();
		try {
			material = msJdbcTemplate.query(queryReadAllSteigerArticles, new Object[] {}, new SteigerArticleMapper(identifierDao));
			return material;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<EldasArticle> readArticlesByAppointmentIdAndIdentifierId(int appointmentId, int identifierId) throws IllegalDataAccessException {
		List<EldasArticle> material = new ArrayList<EldasArticle>();
		try {
			material = eoJdbcTemplate.query(queryReadArticlesByAppointmentId, new Object[] { appointmentId }, new EldasArticleMapper(identifierDao));
			return material;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	public String saveEldasArticle(final EldasArticle article) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final User currentUser = UserUtils.getCurrentUser();
		try {
			eoJdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryCreateEldasArticle, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, article.getENumber());
					ps.setString(2, article.getDescription());
					ps.setDouble(3, article.getQuantity());
					ps.setString(4, article.getUom());
					ps.setInt(5, article.getAppointmentId());
					ps.setInt(6, null != article.getMaterialIdentifier() ? article.getMaterialIdentifier().getId() : -1);
					ps.setInt(7, 0);
					ps.setString(8, currentUser.getFullName());
					ps.setTimestamp(9, DateUtils.getCurrentTimestamp());
					return ps;
				}
			}, keyHolder);
			log.debug("Inserted charge with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	public String saveSteigerArticle(final SteigerArticle article) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final User currentUser = UserUtils.getCurrentUser();
		try {
			eoJdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryCreateEldasArticle, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, article.getArticleNumber());
					ps.setString(2, article.getDescription());
					ps.setString(3, article.getProducer());
					ps.setInt(4, article.getAppointmentId());
					ps.setInt(5, null != article.getMaterialIdentifier() ? article.getMaterialIdentifier().getId() : -1);
					ps.setInt(6, 0);
					ps.setString(7, currentUser.getFullName());
					ps.setTimestamp(8, DateUtils.getCurrentTimestamp());
					return ps;
				}
			}, keyHolder);
			log.debug("Inserted charge with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}
}
